USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetStatusXml')
	DROP FUNCTION dbo.GetStatusXml
GO


CREATE FUNCTION [dbo].[GetStatusXml] (@profileName VARCHAR(200)) 
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@result			varchar(max)

	SET @result = '<STATUS><entry id="0" type=""></entry>'

	select @result = @result + '<entry id="' + CAST([Status] AS VARCHAR) + '" type="'+StatusProfile+'"><![CDATA[' +  StatusDesc + ']]></entry>'
	FROM TR_STATUS 
	WHERE StatusProfile = (CASE WHEN ISNULL(@profileName, '') = '' THEN StatusProfile ELSE @profileName END)
	Order by StatusProfile, [Status]
	
	SET @result = @result + '</STATUS>'

	return @result

END


-- SELECT dbo.GetStatusXml('') Activity')